package Persistance;

import java.sql.*;
import java.util.LinkedList;
import java.util.Collection;

import org.apache.log4j.Logger;
import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;
import BusinessLogic.Exceptions.DataAlreadyExistException;

public class MySqlUtilityManagment {

    static Logger logger = Logger.getLogger("MovieSystem.Persistance.MySqlUtilityManagment");

    private Connection conn;
    private String address;

    public MySqlUtilityManagment(String server,String port,String schema,String username,String password) {
        String[] details = {server,port,schema,username,password};
        address = new PrintfFormat("jdbc:mysql://%s:%s/%s?user=%s&password=%s").sprintf(details);
    }

    public void connect(){
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            logger.info("connecting to server: '"+address+"'");
            conn = DriverManager.getConnection(address);
        } catch (SQLException ex) {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        } catch (IllegalAccessException e) {
            e.printStackTrace(); 
        } catch (ClassNotFoundException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } catch (InstantiationException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }

    public ResultSet getData(String table, String filter) {
        Statement stmt;
        ResultSet rs = null;

        try {            
            String sql = "SELECT * FROM "+table;
            if (filter != null && !filter.equals("") && filter.indexOf("all") < 0)
                sql += " WHERE "+filter;
            logger.debug("runnning sql statement: "+sql);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;
    }

    public String[] searchData(String table, String column, String filter, int start, int amount) {
        Statement stmt = null;
        ResultSet rs = null;
        LinkedList<String> l = new LinkedList<String>();
        String sql;
        try {
            stmt = conn.createStatement();
            sql = "SELECT "+column+" FROM "+table;
            if (filter != null && !filter.equals("") && filter.indexOf("all") < 0)
                sql += " WHERE "+filter;
            sql += " ORDER BY "+column+" LIMIT "+start+","+amount;
            logger.debug("runnning sql statement: "+sql);
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                l.add((String) rs.getObject(column));
                logger.debug("--------" + (String) rs.getObject(column));
            }            
            rs.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        String[] res = new String[l.size()];
        return l.toArray(res);
    }

    public Collection<String> searchData(String table, String column, String filter, String addition) {
        Statement stmt = null;
        ResultSet rs = null;
        LinkedList<String> l = new LinkedList<String>();
        String sql;
        try {
            stmt = conn.createStatement();
            sql = "SELECT "+column+" FROM "+table;
            if (filter!=null && filter.indexOf("all") < 0)
                sql += " WHERE "+filter;
            if (addition!=null)
                sql += " "+addition;
            logger.debug("runnning sql statement: "+sql);
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                if (rs.getObject(column) != null )
                {
                    l.add(rs.getObject(column).toString());
                	logger.debug(rs.getObject(column).toString());
                }
            }
            rs.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    	logger.debug("Size = " +  l.size());
        return l;
    }

    public Collection<String> searchData(String table, String column, String filter) {
        return searchData(table,column,filter,null);
    }

    public void deleteData(String table, String filter) {
        Statement stmt = null;

        try {
            String sql = "DELETE FROM "+table+" WHERE "+filter;
            logger.debug("runnning sql statement: "+sql);
            stmt = conn.createStatement();
            stmt.executeUpdate(sql);
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally{
            if (stmt != null){
                try {
                    stmt.close();
                }
                catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }               
    }

    public void addData(String table, String columns, String values) throws DataAlreadyExistException {
        Statement stmt = null;

        try {
            String sql = "INSERT INTO "+table+" ("+columns+") VALUES ("+values+")";
            logger.debug("runnning sql statement: "+sql);
            stmt = conn.createStatement();
            stmt.executeUpdate(sql);
        }
        catch (MySQLIntegrityConstraintViolationException e) {
            throw new DataAlreadyExistException(values);
        }
        catch (SQLException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
        finally{
            if (stmt != null){
                try {
                    stmt.close();
                }
                catch (SQLException e) {
                    e.printStackTrace(); 
                }
            }
        }
    }

    public ResultSet getData(String table, String filter, int start, int amount) {
        Statement stmt;
        ResultSet rs = null;

        try {
            String sql = "SELECT * FROM "+table;
            if (filter.indexOf("all") < 0)
                sql += " WHERE "+filter;
            sql += " LIMIT "+start+","+amount;
            logger.debug("runnning sql statement: "+sql);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;
    }
}
